/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.server.BUS;

import db.server.DAO.DesignDAO;
import db.server.DAO.EditDAO;
import db.server.DAO.QueryDAO;
import db.server.DTO.DatabaseDTO;
import db.server.DTO.DeleteQueryDTO;
import db.server.DTO.DesignTableDTO;
import db.server.DTO.EditTableDTO;
import db.server.DTO.ExpressionDTO;
import db.server.DTO.InsertQueryDTO;
import db.server.DTO.QueryDTO;
import db.server.DTO.SelectQueryDTO;
import db.server.DTO.UpdateQueryDTO;

import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.ParserConfigurationException;

/**
 *
 * @author QuangKhai
 */
public class QueryBUS {
    private QueryDAO queryDAO = new QueryDAO();
    private EditDAO editDAO = new EditDAO();
    private DesignDAO designDAO = new DesignDAO();
    
    ///Save query to file
    public void saveQuery(QueryDTO queryDTO, String path) throws ParserConfigurationException{
        queryDAO.writeQuery(queryDTO, path);
    }
    
    ///Read query file to generate QueryDTO
    public QueryDTO readQueryFile(String path){
        return queryDAO.readQueryFile(path);
    }
    
    ///Get update results by input values
    public EditTableDTO getUpdateResults(String sql, DatabaseDTO databaseDTO) throws ParserConfigurationException{
        UpdateQueryDTO updateDTO = new UpdateQueryDTO();
        
        updateDTO.setUpdate(sql.split("set")[0].substring(7).trim());
        
        List<String> columnsName = new ArrayList<String>();
        List<String> value = new ArrayList<String>();
            
        String where = null, values;  
        if(sql.split("set")[1].contains("where")){
            values = sql.split("set")[1].split("where")[0];
            where = sql.split("set")[1].split("where")[1];
            
            if(where.contains("and")){
                updateDTO.setLinkWhere("and");
            }else{
                updateDTO.setLinkWhere("or");
            }
            
            updateDTO.setWhere(getWhere(where));
        }else{
            values = sql.split("set")[1];            
        }
        
        for(int i = 0; i < values.split(",").length; i++){
            columnsName.add(values.split("=")[0].trim());
            value.add(getInsertDTOValue(values.split("=")[1]));
        }
        
        updateDTO.setValues(value);
        updateDTO.setColumnsName(columnsName);
        
        return updateReults(updateDTO, databaseDTO, where);
    }
    
    ///Processing input update's values
    public EditTableDTO updateReults(UpdateQueryDTO update, DatabaseDTO databaseDTO, String where) throws ParserConfigurationException{
        DesignTableDTO designTable = new DesignTableDTO();
        
        for(int i = 0; i < databaseDTO.getLstTableDTO().size(); i++){
            if(databaseDTO.getLstTableDTO().get(i).getTableName().compareTo(update.getUpdate())== 0){                    
                designTable = databaseDTO.getLstTableDTO().get(i);
                break;
            }
        }
        
        String designPath = designTable.getTablePath();
        String editPath = designPath.substring(0, designPath.length() - 11) + "-Edit.xml";
        
        EditTableDTO lstValues = editDAO.readEditTable(editPath, designTable);
        
        if(!update.getWhere().isEmpty()){
            if(update.getLinkWhere().compareTo("and") == 0){
                String select = "select * from " + update.getUpdate() + " where " + where;
                                
                List<Integer> position = new ArrayList<Integer>();                                
                EditTableDTO selectValues = getSelectResults(select, databaseDTO);
                
                for(int i = 0; i < lstValues.getLstValues().size(); i++){
                    for(int j = 0; j < selectValues.getLstValues().size(); j++){
                        if(lstValues.getLstValues().get(i).get(0).compareTo(selectValues.getLstValues().get(j).get(0)) == 0){
                            position.add(i);
                            break;
                        }
                    }
                }
                
                selectValues = doUpdateValue(selectValues, update.getWhere().get(0), update);
                selectValues = doUpdateValue(selectValues, update.getWhere().get(1), update);
                
                for(int i = 0; i < selectValues.getLstValues().size(); i++){
                    lstValues.getLstValues().set(position.get(i), selectValues.getLstValues().get(i));
                }
            }else if(update.getLinkWhere().compareTo("or") == 0){
                lstValues = doUpdateValue(lstValues, update.getWhere().get(0), update);
                lstValues = doUpdateValue(lstValues, update.getWhere().get(1), update);
            }else{
                lstValues = doUpdateValue(lstValues, update.getWhere().get(0), update);
            }
        }else{           
            
            for(int i = 0; i < lstValues.getColumnName().size(); i++){
                for(int j = 0; j < update.getColumnsName().size(); j++){
                    if(lstValues.getColumnName().get(i).compareTo(update.getColumnsName().get(j)) == 0){
                        for(int k = 0; k < lstValues.getLstValues().size(); k++){
                            lstValues.getLstValues().get(k).set(i, update.getValues().get(j));
                        }
                    }
                }
            }
        }
        
        List<String> writeResults = new ArrayList<String>();        
        writeResults.add(editPath);
        for(int i = 0; i < lstValues.getLstValues().size(); i++){
            for(int j = 0; j < lstValues.getColumnName().size(); j++){
                writeResults.add(lstValues.getLstValues().get(i).get(j));
            }
        }   
        
        editDAO.writeEditTable(writeResults, designTable, null);
        
        return lstValues;
    }
    
    ///Processing update
    public EditTableDTO doUpdateValue(EditTableDTO editTableDTO, ExpressionDTO exp, UpdateQueryDTO update){
        List<Integer> getValuePosition = new ArrayList<Integer>();
        int getCheckPosition = 0;        
        
        for(int i = 0; i < editTableDTO.getColumnName().size(); i++){
            for(int j = 0; j < update.getColumnsName().size(); j++){
                if(editTableDTO.getColumnName().get(i).compareTo(update.getColumnsName().get(j)) == 0){
                    getValuePosition.add(j);                    
                }
                
                if(editTableDTO.getColumnName().get(i).compareTo(exp.getFirstValue()) == 0){
                    getCheckPosition = i;                           
                }
            }
        }
        
        for(int i = 0; i < editTableDTO.getLstValues().size(); i++){
            if(checkDataType(editTableDTO.getLstValues().get(i).get(getCheckPosition))){               
                for(int j = 0; j < getValuePosition.size(); j++){
                    int firstValue = Integer.parseInt(editTableDTO.getLstValues().get(i).get(getCheckPosition));
                    int operator = exp.getOperator();
                    int check = Integer.parseInt(exp.getLastValue());

                    if(checkNumberExpression(firstValue, operator, check)){
                        editTableDTO.getLstValues().get(i).set(getCheckPosition, update.getValues().get(getValuePosition.get(j)));
                    }
                }
            } else {
                for(int j = 0; j < getValuePosition.size(); j++){  
                    if(editTableDTO.getLstValues().get(i).get(getCheckPosition).compareTo(exp.getLastValue()) == 0){
                        editTableDTO.getLstValues().get(i).set(getCheckPosition, update.getValues().get(getValuePosition.get(j)));
                    }
                }
            }            
        }
        
        return editTableDTO;
    }
    
    
    ///Get insert results
    public EditTableDTO getInsertResults(String sql, DatabaseDTO databaseDTO) throws ParserConfigurationException{
        InsertQueryDTO insertDTO = new InsertQueryDTO();
        DesignTableDTO designTable = new DesignTableDTO();
        String regex = "\\(";
        
        if(sql.contains("select")){
            String info = sql.split("select")[0].substring(12);
            insertDTO.setInsert(info.split(regex)[0].trim());
            
            for(int i = 0; i < databaseDTO.getLstTableDTO().size(); i++){
                if(databaseDTO.getLstTableDTO().get(i).getTableName().compareTo(info.split(regex)[0].trim())== 0){                    
                    designTable = databaseDTO.getLstTableDTO().get(i);
                    break;
                }
            }
            
            info = info.split(regex)[1];
            
            List<String> columnsName = new ArrayList<String>();            
            for(int i = 0; i < info.split(",").length; i++){
                String s = getInsertDTOValue(info.split(",")[i]);
                columnsName.add(s);
            }                       
            insertDTO.setColumnsName(columnsName);
            
            String value = "select " + sql.split("select")[1];
            
            insertDTO.setValues(getSelectResults(value, databaseDTO).getLstValues());
        }else{
            String info = sql.split("values")[0].substring(12);
            int columnsCount = 0;
            
            if(info.contains("(")){
                for(int i = 0; i < databaseDTO.getLstTableDTO().size(); i++){
                    if(databaseDTO.getLstTableDTO().get(i).getTableName().compareTo(info.split(regex)[0].trim())== 0){
                        columnsCount = databaseDTO.getLstTableDTO().get(i).getLstRows().size();
                        designTable = databaseDTO.getLstTableDTO().get(i);
                        break;
                    }
                }
                
                insertDTO.setInsert(info.split(regex)[0].trim());            
                info = info.split(regex)[1];

                List<String> columnsName = new ArrayList<String>();            
                for(int i = 0; i < info.split("\\,").length; i++){
                    columnsName.add(getInsertDTOValue(info.split(",")[i]));
                }           
                columnsCount = columnsName.size();
                insertDTO.setColumnsName(columnsName);
            } else {
                insertDTO.setInsert(info.trim());
                List<String> columnsName = new ArrayList<String>();            
                
                for(int i = 0; i < databaseDTO.getLstTableDTO().size(); i++){
                    if(databaseDTO.getLstTableDTO().get(i).getTableName().compareTo(info.trim())== 0){
                        columnsCount = databaseDTO.getLstTableDTO().get(i).getLstRows().size();
                        designTable = databaseDTO.getLstTableDTO().get(i);
                                                
                        for(int j = 0; j < columnsCount; j++){
                            columnsName.add(databaseDTO.getLstTableDTO().get(i).getLstRows().get(j).getColumnName());
                        }
                    }
                }
                
                insertDTO.setColumnsName(columnsName);
            }
            
            String value = sql.split("values")[1];
            List<List<String>> lstValues = new ArrayList<List<String>>();
            
            for(int i = 0; i < value.split(",").length; i++){
                if(i % columnsCount == 0){
                    List<String> values = new ArrayList<String>();
                    
                    for(int j = 0; j < columnsCount; j++){
                        String str = getInsertDTOValue(value.split(",")[i + j]);
                        values.add(str);
                    }
                    
                    lstValues.add(values);
                }
            } 
            
            insertDTO.setValues(lstValues);
        }        
        
        return insertResult(insertDTO, designTable);
    }
        
    ///Processing to get insert results
    public EditTableDTO insertResult(InsertQueryDTO insert, DesignTableDTO designTableDTO) throws ParserConfigurationException{
        EditTableDTO results = new EditTableDTO();
        String designPath = designTableDTO.getTablePath();
        String saveFileName = designPath.substring(0, designPath.length() - 11) + "-Edit.xml";
        
        List<String> columnsName = new ArrayList<String>();
        
        for(int i = 0; i < designTableDTO.getLstRows().size(); i++){
            columnsName.add(designTableDTO.getLstRows().get(i).getColumnName());
        }
        
        List<List<String>> lstValues = new ArrayList<List<String>>();
        lstValues = editDAO.readEditTable(saveFileName, designTableDTO).getLstValues();
        
        boolean check = false;
        for(int k = 0; k < insert.getValues().size(); k++){
            List<String> values = new ArrayList<String>();
            int m = 0;
            for(int i = 0; i < designTableDTO.getLstRows().size(); i++){
                for(int j = 0; j < insert.getColumnsName().size(); j++){
                    if(insert.getColumnsName().get(j).compareTo(designTableDTO.getLstRows().get(i).getColumnName()) == 0){
                        check = true;
                    }
                }

                if(check){
                    values.add(insert.getValues().get(k).get(m));
                    m++;
                    check = false;
                }else{
                    if(!designTableDTO.getLstRows().get(i).isAllowNulls() || designTableDTO.getLstRows().get(i).isPrimaryKey()){
                        return null;
                    }else{
                        values.add("");                        
                    }
                }
            }
            
            lstValues.add(values);                    
        }
                
        results.setColumnName(columnsName);
        results.setLstValues(lstValues);
        
        List<String> writeResults = new ArrayList<String>();        
        
        writeResults.add(saveFileName);
        for(int i = 0; i < results.getLstValues().size(); i++){
            for(int j = 0; j < results.getColumnName().size(); j++){
                writeResults.add(results.getLstValues().get(i).get(j));
            }
        }
        
        editDAO.writeEditTable(writeResults, designTableDTO, null);
        
        return results;
    }
    
    //Remove " ( ) , in string
    public String getInsertDTOValue(String str){        
        for(int i = 0; i < str.length(); i++){
            if(str.charAt(i) == '\"' || str.charAt(i) == '(' || str.charAt(i) == ')' || str.charAt(i) == ','){
                String temp01 = str.substring(0, i);
                String temp02 = str.substring(i + 1);
                str = temp01 + temp02;
                i = i - 1;
            }
        }
        
        while(str.charAt(0) == ' '){
            str = str.substring(1);
        }
        
        while(str.charAt(str.length() - 1) == ' '){
            str = str.substring(0, str.length() - 1);
        }        
        
        return str;
    }
    
    ///Get delete results    
    public EditTableDTO getDeleteResults(String sql, DatabaseDTO databaseDTO) throws ParserConfigurationException{
        DeleteQueryDTO deleteDTO = new DeleteQueryDTO();
        
        if(sql.contains("where")){
            String from = sql.split("where")[0];
            deleteDTO.setFrom(getElement(from.substring(11, from.length()).trim()));
            deleteDTO.setWhere(getWhere(sql.split("where")[1]));
            
            if(sql.split("where")[1].contains("and")){
                deleteDTO.setLinkWhere("and");
            }else if(sql.split("where")[1].contains("or")){
                deleteDTO.setLinkWhere("or");
            }else{
                deleteDTO.setLinkWhere("");
            }
        }else{
            String from = sql.substring(11, sql.length());
            deleteDTO.setFrom(getElement((from.trim())));
        }        
        
        
        return deleteResult(deleteDTO, databaseDTO);
    }
    
    ///Processing delete
    public EditTableDTO deleteResult(DeleteQueryDTO delete, DatabaseDTO databaseDTO) throws ParserConfigurationException{
        EditTableDTO results = new EditTableDTO();
        EditTableDTO values = new EditTableDTO();
        String editPath = "";
        String designPath = "";

        for(int i = 0; i < delete.getFrom().size(); i++){            
            for(int j = 0; j < databaseDTO.getLstTableDTO().size(); j++){
                if(delete.getFrom().get(i).compareTo(databaseDTO.getLstTableDTO().get(j).getTableName()) == 0){
                    designPath = databaseDTO.getLstTableDTO().get(j).getTablePath();
                    editPath = designPath.substring(0, designPath.length() - 11) + "-Edit.xml";
                    values = editDAO.readEditTable(editPath, databaseDTO.getLstTableDTO().get(j));                    
                }
            }
        } 
        
        if(delete.getFrom().size() != 1){
            for(int i = 0; i < delete.getFrom().size(); i++){            
                for(int j = 0; j < databaseDTO.getLstTableDTO().size(); j++){
                    if(delete.getFrom().get(i).compareTo(databaseDTO.getLstTableDTO().get(j).getTableName()) == 0){
                        designPath = databaseDTO.getLstTableDTO().get(j).getTablePath();
                        editPath = designPath.substring(0, designPath.length() - 11) + "-Edit.xml";
                        editDAO.writeEditTable(null, null, editPath);                        
                    }
                }
            } 
            return null;
        }else{
            if(!delete.getWhere().isEmpty()){ 
                EditTableDTO deleteValues = new EditTableDTO();
                
                DesignTableDTO designTableDTO = designDAO.readDesignTable(designPath);
                List<String> lstSelect = new ArrayList<String>();
                for(int i = 0; i < values.getColumnName().size(); i++){
                    lstSelect.add(values.getColumnName().get(i));
                }
                    
                if(delete.getLinkWhere().compareTo("and") == 0){
                    deleteValues = getSelectValue(values, lstSelect, delete.getWhere().get(0));                    
                    deleteValues = getSelectValue(values, lstSelect, delete.getWhere().get(1));
                    
                    return doDeleteValue(values, deleteValues, editPath, designTableDTO);                 
                    
                } else if(delete.getLinkWhere().compareTo("or") == 0){
                    EditTableDTO deleteValues01 = getSelectValue(values, lstSelect, delete.getWhere().get(0));
                    EditTableDTO deleteValues02 = getSelectValue(values, lstSelect, delete.getWhere().get(1));
                    
                    List<List<String>> lstValues = new ArrayList<List<String>>();
                    
                    for(int k = 0; k < deleteValues01.getLstValues().size(); k++){
                        lstValues.add(deleteValues01.getLstValues().get(k));
                    }
                    
                    for(int k = 0; k < deleteValues02.getLstValues().size(); k++){
                        lstValues.add(deleteValues02.getLstValues().get(k));
                    }
                    
                    deleteValues.setColumnName(lstSelect);
                    deleteValues.setLstValues(lstValues);
                    
                    return doDeleteValue(values, deleteValues, editPath, designTableDTO);
                } else {
                    deleteValues = getSelectValue(values, lstSelect, delete.getWhere().get(0));
                    return doDeleteValue(values, deleteValues, editPath, designTableDTO);
                }
            }else{
                for(int j = 0; j < databaseDTO.getLstTableDTO().size(); j++){
                    if(delete.getFrom().get(0).compareTo(databaseDTO.getLstTableDTO().get(j).getTableName()) == 0){
                        designPath = databaseDTO.getLstTableDTO().get(j).getTablePath();
                        editPath = designPath.substring(0, designPath.length() - 11) + "-Edit.xml";
                        editDAO.writeEditTable(null, null, editPath);
                        return null;
                    }
                }
            }
        }
        
        return results;
    }
        
    ///Execute delete
    public EditTableDTO doDeleteValue(EditTableDTO values, EditTableDTO deleteValues, String saveFileName, DesignTableDTO designTable) throws ParserConfigurationException{
        List<String> results = new ArrayList<String>();
        for(int i = 0; i < values.getLstValues().size(); i++){
            for(int j = 0; j < deleteValues.getLstValues().size(); j++){
                if(values.getLstValues().get(i).get(0).compareTo(deleteValues.getLstValues().get(j).get(0)) == 0){
                    values.getLstValues().remove(i);
                }
            }
        }
        
        results.add(saveFileName);
        for(int i = 0; i < values.getLstValues().size(); i++){
            for(int j = 0; j < values.getColumnName().size(); j++){
                results.add(values.getLstValues().get(i).get(j));
            }
        }
        
        editDAO.writeEditTable(results, designTable, null);
        return values;
    }
    
    ///Get select results
    public EditTableDTO getSelectResults(String sql, DatabaseDTO databaseDTO){
        SelectQueryDTO selectDTO = new SelectQueryDTO();
        
        String sqlSubSelect = sql.split("select")[1];
        String sqlSubFrom = sqlSubSelect.split("from")[1];
        
        if(sql.contains("where")){
            selectDTO.setFrom(getElement(sqlSubFrom.split("where")[0]));
            selectDTO.setWhere(getWhere(sqlSubFrom.split("where")[1]));
            
            if(sqlSubFrom.split("where")[1].contains("and")){
                selectDTO.setLinkWhere("and");
            }else if(sqlSubFrom.split("where")[1].contains("or")){
                selectDTO.setLinkWhere("or");
            }else{
                selectDTO.setLinkWhere("");
            }
        }else{
            selectDTO.setFrom(getElement((sqlSubFrom.trim())));
        }        
        
        if(getElement(sqlSubSelect.split("from")[0]).get(0).compareTo("*") == 0){   
            List<String> select = new ArrayList<String>();
            
            for(int i = 0; i < databaseDTO.getLstTableDTO().size(); i++){
                if(databaseDTO.getLstTableDTO().get(i).getTableName().compareTo(selectDTO.getFrom().get(0))== 0){                    
                    for(int j = 0; j < databaseDTO.getLstTableDTO().get(i).getLstRows().size(); j++){
                        select.add(databaseDTO.getLstTableDTO().get(i).getLstRows().get(j).getColumnName());
                    }
                    break;
                }
            }
            
            selectDTO.setSelect(select);
        }else{
            selectDTO.setSelect(getElement(sqlSubSelect.split("from")[0]));
        }
        
        return selectResults(selectDTO, databaseDTO);
    }
    
    ///Get element such as attribute, table name, expression of input string
    public List<String> getElement(String str){
        List<String> lst = new ArrayList<String>();
        
        if(str.compareTo("*") == 0){
            lst.add("*");
        } else {
            String[] lstTemp = str.split(",");
            
            for(int i = 0; i < lstTemp.length; i++)
                lst.add(lstTemp[i].trim());
        }        
        
        return lst;
    }
    
    //whereList: atrribute/ operator/ value
    public List<ExpressionDTO> getWhere(String where){
        List<ExpressionDTO> lstWhere = new ArrayList<ExpressionDTO>();
        
        if(where.contains("and")){
            lstWhere.add(getExpression(where.split("and")[0]));
            lstWhere.add(getExpression(where.split("and")[1]));
        } else if(where.contains("or")){            
            lstWhere.add(getExpression(where.split("or")[0]));
            lstWhere.add(getExpression(where.split("or")[1]));
        }else{
            lstWhere.add(getExpression(where));
        }
        
        return lstWhere;
    }
    
    ///Get expression of input string after where
    public ExpressionDTO getExpression(String expression){
        ExpressionDTO exp = new ExpressionDTO();
        
        String firstValue = expression.split(checkOperator(expression).get(0))[0].trim();
        
        if(firstValue.contains(".")){
            exp.setFirstValue(firstValue.split("\\.")[1]);
        }else{
            exp.setFirstValue(firstValue);
        }
        
        String lastValue = expression.split(checkOperator(expression).get(0))[1].trim();
        
        if(lastValue.contains(".")){
            exp.setLastValue(lastValue.split("\\.")[1]);
        }
        
        if(lastValue.contains("\"")){
            lastValue = getInsertDTOValue(lastValue);
            exp.setLastValue(lastValue);
        }
        
        exp.setOperator(Integer.parseInt(checkOperator(expression).get(1)));
            
        return exp;
    }
        
    ///Check operator correct?
    public List<String> checkOperator(String expression){ 
        List<String> lst = new ArrayList<String>();
        
        if(expression.contains(">") && !expression.contains("=")){
            lst.add(">");
            lst.add("1");
            return lst;
        }

        if(expression.contains(">=")){
            lst.add(">=");
            lst.add("2");
            return lst;
        }

        if(expression.contains("<") && !expression.contains("=")){
            lst.add("<");
            lst.add("3");
            return lst;
        }

        if(expression.contains("<=")){
            lst.add("<=");
            lst.add("4");
            return lst;
        }

        if(expression.contains("==")){
            lst.add("==");
            lst.add("5");
            return lst;
        }
        
        if(expression.contains("!=")){
            lst.add("!=");
            lst.add("6");
            return lst;
        }
        
        return null;
    }
 
    ///Processing select
    public EditTableDTO selectResults(SelectQueryDTO select, DatabaseDTO databaseDTO){
        EditTableDTO results = new EditTableDTO();
        List<EditTableDTO> lstEditTable = new ArrayList<EditTableDTO>();        
        
        for(int i = 0; i < select.getFrom().size(); i++){            
            for(int j = 0; j < databaseDTO.getLstTableDTO().size(); j++){
                if(select.getFrom().get(i).compareTo(databaseDTO.getLstTableDTO().get(j).getTableName()) == 0){
                    String designPath = databaseDTO.getLstTableDTO().get(j).getTablePath();
                    String path = designPath.substring(0, designPath.length() - 11) + "-Edit.xml";
                    EditTableDTO editTableDTO = editDAO.readEditTable(path, databaseDTO.getLstTableDTO().get(j));
                    lstEditTable.add(editTableDTO);
                }
            }
        } 

        if(select.getFrom().size() == 2){
            if(select.getWhere().size() == 1){
                results = mergeTable(lstEditTable, select.getWhere().get(0));
            }else{
                results = mergeTable(lstEditTable, select.getWhere().get(0));
                results = getSelectValue(results, select.getSelect(), select.getWhere().get(1));
            }
        }else{
            if(!select.getWhere().isEmpty()){                
                if(select.getLinkWhere().compareTo("and") == 0){
                    results = lstEditTable.get(0);           
                    EditTableDTO results_01 = getSelectValue(results, select.getSelect(), select.getWhere().get(0));
                    EditTableDTO results_02 = getSelectValue(results, select.getSelect(), select.getWhere().get(1));
                    
                    List<String> columnsName = new ArrayList<String>();
                    List<List<String>> lstValues = new ArrayList<List<String>>();
                    
                    columnsName = results_01.getColumnName();
                    
                    if(select.getWhere().get(0).getFirstValue().compareTo(select.getWhere().get(1).getFirstValue()) == 0){
                        for(int i = 0; i < results_01.getLstValues().size(); i++){
                            lstValues.add(results_01.getLstValues().get(i));
                        }
                        
                        for(int i = 0; i < results_02.getLstValues().size(); i++){
                            lstValues.add(results_02.getLstValues().get(i));
                        }
                    } else {
                        for(int i = 0; i < results_01.getLstValues().size(); i++){
                            for(int j = 0; j < results_02.getLstValues().size(); j++){
                                if(compareList(results_01.getLstValues().get(i), results_02.getLstValues().get(j))){
                                    lstValues.add(results_01.getLstValues().get(i));
                                }
                            }
                        }
                    }
                    
                    results.setColumnName(columnsName);
                    results.setLstValues(lstValues);
                } else if(select.getLinkWhere().compareTo("or") == 0){
                    EditTableDTO temp01 = getSelectValue(lstEditTable.get(0), select.getSelect(), select.getWhere().get(0));
                    EditTableDTO temp02 = getSelectValue(lstEditTable.get(0), select.getSelect(), select.getWhere().get(1));
                    List<List<String>> lstValues = new ArrayList<List<String>>();
                    
                    for(int k = 0; k < temp01.getLstValues().size(); k++){
                        lstValues.add(temp01.getLstValues().get(k));
                    }
                    
                    for(int k = 0; k < temp02.getLstValues().size(); k++){
                        lstValues.add(temp02.getLstValues().get(k));
                    }
                    
                    results.setColumnName(temp01.getColumnName());
                    results.setLstValues(lstValues);
                } else {
                    results = getSelectValue(lstEditTable.get(0), select.getSelect(), select.getWhere().get(0));
                }
            }else{
                results = getSelectValue(lstEditTable.get(0), select.getSelect(), null);
            }
        }
        
        return results;
    }
    
    ///Compare 2 list
    public boolean compareList(List<String> lst_01, List<String> lst_02){
        if(lst_01.size() != lst_02.size()){
            return false;
        } else{
            for(int i = 0; i < lst_01.size(); i++){
                if(lst_01.get(i).compareTo(lst_02.get(i)) != 0){
                    return false;
                }
            }
        }
        
        return true;
    }
    
    ///Get select results
    public EditTableDTO getSelectValue(EditTableDTO editTableDTO, List<String> select, ExpressionDTO exp){
        EditTableDTO selectValue = new EditTableDTO();
        
        List<String> lstColumnName = new ArrayList<String>();
        List<List<String>> lstValues = new ArrayList<List<String>>();
        
        List<Integer> getValuePosition = new ArrayList<Integer>();
        int getCheckPosition = 0;        
        
        for(int i = 0; i < editTableDTO.getColumnName().size(); i++){
            for(int j = 0; j < select.size(); j++){
                if(editTableDTO.getColumnName().get(i).compareTo(select.get(j)) == 0){
                    getValuePosition.add(i);
                    lstColumnName.add(select.get(j));
                }
                
                if(exp != null && editTableDTO.getColumnName().get(i).compareTo(exp.getFirstValue()) == 0){
                    getCheckPosition = i;                           
                }
            }
        }
        
        for(int i = 0; i < editTableDTO.getLstValues().size(); i++){
            List<String> value = new ArrayList<String>();
            
            if(exp != null){
                if(checkDataType(editTableDTO.getLstValues().get(i).get(getCheckPosition))){               
                    for(int j = 0; j < getValuePosition.size(); j++){
                        int firstValue = Integer.parseInt(editTableDTO.getLstValues().get(i).get(getCheckPosition));
                        int operator = exp.getOperator();
                        int check = Integer.parseInt(exp.getLastValue());

                        if(checkNumberExpression(firstValue, operator, check)){
                            value.add(editTableDTO.getLstValues().get(i).get(getValuePosition.get(j)));
                        }
                    }
                } else {
                    for(int j = 0; j < getValuePosition.size(); j++){  
                        if(editTableDTO.getLstValues().get(i).get(getCheckPosition).compareTo(exp.getLastValue()) == 0){
                            value.add(editTableDTO.getLstValues().get(i).get(getValuePosition.get(j)));
                        }
                    }
                }
            }else{
                for(int j = 0; j < getValuePosition.size(); j++){  
                    value.add(editTableDTO.getLstValues().get(i).get(getValuePosition.get(j)));
                }
            }
            
            if(!value.isEmpty()){
                lstValues.add(value);
            }
        }
        
        selectValue.setColumnName(lstColumnName);
        selectValue.setLstValues(lstValues);
        
        return selectValue;
    }
    
    //Check data type of input values. true: int || false: string
    public boolean checkDataType(String value){      
        try {                  
            Integer.parseInt(value);  
            return true;  
        } catch(Exception e) {  
            return false;  
        }         
    }
    
    ///Merge 2 tables
    public EditTableDTO mergeTable(List<EditTableDTO> lstEditTable, ExpressionDTO Exp){
        EditTableDTO mergeTable = new EditTableDTO();
        
        EditTableDTO editTable_01 = new EditTableDTO();
        editTable_01 = lstEditTable.get(0);
        EditTableDTO editTable_02 = new EditTableDTO();
        editTable_02 = lstEditTable.get(1);
        
        List<String> lstColumnName = new ArrayList<String>();
        List<List<String>> lstValues = new ArrayList<List<String>>();
        
        
        int frist = 0, last = 0;

        for (int j = 0; j < editTable_01.getColumnName().size(); j++) {
            lstColumnName.add(editTable_01.getColumnName().get(j));
            if(editTable_01.getColumnName().get(j).compareTo(Exp.getFirstValue()) == 0){
                frist = j;
            }
        }

        for (int j = 0; j < editTable_02.getColumnName().size(); j++) {
            lstColumnName.add(editTable_02.getColumnName().get(j));
            if(editTable_02.getColumnName().get(j).compareTo(Exp.getLastValue()) == 0){
                last = j;
            }
        }
        
        for(int k = 0; k < editTable_01.getLstValues().size(); k++){             
            List<String> value = new ArrayList<String>();
            String fristValue = editTable_01.getLstValues().get(k).get(frist);

            for(int m = 0; m < editTable_02.getLstValues().size(); m++){
                String lastValue = editTable_02.getLstValues().get(m).get(last);
                if(fristValue.compareTo(lastValue) == 0){
                    for(int n = 0; n < editTable_01.getColumnName().size(); n++){
                        value.add(editTable_01.getLstValues().get(k).get(n));
                    }

                    for(int n = 0; n < editTable_02.getColumnName().size(); n++){
                        value.add(editTable_02.getLstValues().get(m).get(n));
                    }

                    lstValues.add(value);
                }
            }
        }
        
        for(int i = 0; i < lstColumnName.size(); i++){
            for(int j = i + 1; j < lstColumnName.size(); j++){
                if(lstColumnName.get(i).compareTo(lstColumnName.get(j)) == 0){
                    lstColumnName.remove(j);
                    for(int k = 0; k < lstValues.size(); k++){
                        lstValues.get(k).remove(j);
                    }
                    
                    i--;
                    j--;
                }
            }
        }
        
        mergeTable.setColumnName(lstColumnName);
        mergeTable.setLstValues(lstValues);
        
        return mergeTable;
    }
        
    ///Check Expression
    public boolean checkNumberExpression(int value, int operator, int check){
        switch(operator){
            case 1:
                if(value > check)
                    return true;
                break;
            case 2:
                if(value >= check)
                    return true;
                break;
            case 3:
                if(value < check)
                    return true;
                break;
            case 4: 
                if(value <= check)
                    return true;                
                break;
            case 5: 
                if(value == check)
                    return true;
                break;
            case 6:
                if(value != check)
                    return true;
                break;
        }
        
        return false;
    }    
}
